﻿Public Class clsGtProcedureOperatedNgDetails

    Public Function getGtProcedureOperatedNgDetailsList(b_gt_sys_id As String) As DataTable
        Dim szSql As String = ""
        szSql &= " select * from sf.b_gt_procedure_operated_ng_details "
        szSql &= "where b_gt_sys_id = ? and validity = ? order by procedure_no"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, b_gt_sys_id, "1")
        If IsNothing(dt) Then
            getGtProcedureOperatedNgDetailsList = New DataTable
        Else
            getGtProcedureOperatedNgDetailsList = dt
        End If
    End Function

    Public Function getGtProcedureOperatedNgDetials(oper_type As String, b_status As String, procedure_no As String, a_status As Integer) As DataTable
        Dim szSql As String = ""
        szSql &= "select a.*,c.oper_type,c.type_value,c.batch_no,b.oper_num,b.ok_num from sf.b_gt_procedure_operated_ng_details a "
        szSql &= " left join sf.b_gt_procedure_operated_details b on a.b_gt_procedure_operated_details_sys_id = b.sys_id"
        szSql &= " left join sf.b_gt c on a.b_gt_sys_id = c.sys_id"
        szSql &= " where c.oper_type= ? and c.status in (" & b_status & ") and b.procedure_no = ? and b.status = ? order by b.update_time "
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, oper_type, procedure_no, a_status)
        If IsNothing(dt) Then
            getGtProcedureOperatedNgDetials = New DataTable
        Else
            getGtProcedureOperatedNgDetials = dt
        End If
    End Function

    Public Function save(ng_reason As String, sys_id As String) As Integer
        Dim sql As String = ""
        Dim rtn As Integer
        Try
            sql &= "UPDATE sf.b_gt_procedure_operated_ng_details "
            sql &= " SET ng_reason = ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
            sql &= " WHERE sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, ng_reason, sys_id)
        Catch ex As Exception
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function save(en As entity_gt_procedure_operated_ng_details) As Integer
        Dim sql As String = ""
        Dim rtn As Integer = 0
        DBacs.BeginTransaction()
        Try
            If en.b_gt_procedure_operated_details_sys_id > 0 Then
                sql &= "INSERT INTO sf.b_gt_procedure_operated_ng_details(procedure_No, ng_num, ng_reason, operator, validity,b_gt_procedure_operated_details_sys_id, b_gt_sys_id, update_time) "
                sql &= "VALUES ( ?, ?, ?, ?, ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)))"
                With en
                    rtn = DBacs.ExecuteUpdateSql(sql, -1, .procedure_No, .ng_num, .ng_reason, .oper, .validity, .b_gt_procedure_operated_details_sys_id, .b_gt_sys_id)
                End With

                sql = ""
                sql &= "UPDATE sf.b_gt_procedure_operated_details "
                sql &= " SET ng_num = ?, ok_num = oper_num - ?,status = ?, update_time= (SELECT CURRENT_TIMESTAMP(6))"
                sql &= " WHERE sys_id = ? "
                DBacs.ExecuteUpdateSql(sql, -1, en.ng_num, en.ng_num, 1, en.b_gt_procedure_operated_details_sys_id)

                sql = ""
                sql &= " select * from sf.b_gt_procedure_operated_details where sys_id = ? "
                Dim dt3 As DataTable = DBacs.ExecuteSql(sql, -1, en.b_gt_procedure_operated_details_sys_id)

                sql = ""
                sql &= "UPDATE sf.b_gt_procedure_operated "
                sql &= " SET ng_all_num = ng_all_num + ?, ok_all_num = oper_all_num - (ng_all_num + ?), update_time= (SELECT CURRENT_TIMESTAMP(6))"
                sql &= " WHERE sys_id = ? "
                DBacs.ExecuteUpdateSql(sql, -1, en.ng_num, en.ng_num, dt3.Rows(0)("b_gt_procedure_operated_sys_id"))

                sql = ""
                sql &= "UPDATE sf.b_gt_procedure "
                sql &= " SET ng_num = ng_num + ?, ok_num = oper_num - (ng_num + ?), update_time= (SELECT CURRENT_TIMESTAMP(6))"
                sql &= " WHERE sys_id = ? "
                DBacs.ExecuteUpdateSql(sql, -1, en.ng_num, en.ng_num, dt3.Rows(0)("b_gt_procedure_sys_id"))
            Else
                sql &= "INSERT INTO sf.b_gt_procedure_operated_ng_details(procedure_No, ng_num, ng_reason, operator, validity, b_gt_sys_id, update_time) "
                sql &= "VALUES ( ?, ?, ?, ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)))"
                With en
                    rtn = DBacs.ExecuteUpdateSql(sql, -1, .procedure_No, .ng_num, .ng_reason, .oper, .validity, .b_gt_sys_id)
                End With

                sql = ""
                sql &= "UPDATE sf.b_gt_procedure "
                sql &= " SET operable_num = operable_num - ?, update_time= (SELECT CURRENT_TIMESTAMP(6))"
                sql &= " WHERE b_gt_sys_id = ? "
                DBacs.ExecuteUpdateSql(sql, -1, en.ng_num, en.b_gt_sys_id)

            End If

            sql = ""
            sql &= "UPDATE sf.b_gt "
            sql &= " SET ng_num = ng_num + ?, ok_num = ok_num - ?, update_time= (SELECT CURRENT_TIMESTAMP(6))"
            sql &= " WHERE sys_id = ? "
            DBacs.ExecuteUpdateSql(sql, -1, en.ng_num, en.ng_num, en.b_gt_sys_id)

            sql = ""
            sql &= " select * from sf.b_gt_procedure where b_gt_sys_id = ? "
            Dim dt00 As DataTable = DBacs.ExecuteSql(sql, -1, en.b_gt_sys_id)
            Dim tem_operable_num As Integer = Integer.Parse(dt00.Rows(0)("operable_num").ToString())
            If tem_operable_num = 0 Then
                sql = ""
                sql &= "UPDATE sf.b_gt"
                sql &= " SET status = ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
                sql &= " WHERE sys_id = ? "
                rtn = DBacs.ExecuteUpdateSql(sql, -1, "2", en.b_gt_sys_id)
            End If

            sql = ""
            sql &= " select * from sf.b_gt_ng where b_gt_sys_id = ? "
            Dim dt33 As DataTable = DBacs.ExecuteSql(sql, -1, en.b_gt_sys_id)

            sql = ""
            If dt33.Rows.Count = 1 Then
                sql &= "UPDATE sf.b_gt_ng "
                If en.b_gt_procedure_operated_details_sys_id > 0 Then
                    sql &= " SET all_ng_num = all_ng_num + ?,oper_ng_num = oper_ng_num + ?,update_time = (SELECT CURRENT_TIMESTAMP(6)) "
                Else
                    sql &= " SET all_ng_num = all_ng_num + ?,un_oper_ng_num = un_oper_ng_num + ?,update_time = (SELECT CURRENT_TIMESTAMP(6)) "
                End If
                sql &= " WHERE sys_id = ? "
                rtn = DBacs.ExecuteUpdateSql(sql, -1, en.ng_num, en.ng_num, dt33.Rows(0)("sys_id"))
            Else
                If en.b_gt_procedure_operated_details_sys_id > 0 Then
                    sql &= "INSERT INTO sf.b_gt_ng(all_ng_num,oper_ng_num,b_gt_sys_id,update_time) "
                Else
                    sql &= "INSERT INTO sf.b_gt_ng(all_ng_num,un_oper_ng_num,b_gt_sys_id,update_time) "
                End If
                sql &= "VALUES (?,?,?,(SELECT CURRENT_TIMESTAMP(6)))"
                rtn = DBacs.ExecuteUpdateSql(sql, -1, en.ng_num, en.ng_num, en.b_gt_sys_id)
            End If

            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function updateGtProcedureOperatedNgDetailsVlidity(sys_id As String,
                                                              procedure_no As Integer,
                                                              ng_num As String,
                                                              b_gt_procedure_operated_details_sys_id As String,
                                                              b_gt_sys_id As String) As Integer
        Dim szSql As String = String.Empty
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            szSql &= "update sf.b_gt_procedure_operated_ng_details set ng_num = ? where sys_id = ?"
            rtn = DBacs.ExecuteUpdateSql(szSql, -1, ng_num, sys_id)
            If procedure_no > 0 Then
                szSql = String.Empty
                szSql &= "UPDATE sf.b_gt_procedure_operated_details "
                szSql &= " SET ng_num = ?, ok_num = oper_num - ? update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE sys_id = ? "
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, ng_num, b_gt_procedure_operated_details_sys_id)

                szSql = ""
                szSql &= " select * from sf.b_gt_procedure_operated_details where sys_id = ? "
                Dim dt3 As DataTable = DBacs.ExecuteSql(szSql, -1, b_gt_procedure_operated_details_sys_id)

                szSql = ""
                szSql &= " select * from sf.b_gt_procedure_operated_details where b_gt_procedure_operated_sys_id = ? and procedure_no = ?"
                Dim dt33 As DataTable = DBacs.ExecuteSql(szSql, -1, dt3.Rows(0)("b_gt_procedure_operated_sys_id"), procedure_no.ToString())

                Dim dt33_ng_num As Object = dt33.Compute("sum(ng_num)", "")
                Dim dt33_ok_num As Object = dt33.Compute("sum(ok_num)", "")

                szSql = String.Empty
                szSql &= "UPDATE sf.b_gt_procedure_operated "
                szSql &= " SET ng_all_num = ?, ok_all_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                szSql &= " WHERE sys_id = ? "
                rtn = DBacs.ExecuteUpdateSql(szSql, -1, dt33_ng_num, dt33_ok_num, dt3.Rows(0)("b_gt_procedure_operated_sys_id"))

                szSql = ""
                szSql &= " select * from sf.b_gt_procedure_operated where sys_id = ? "
                Dim dt2 As DataTable = DBacs.ExecuteSql(szSql, -1, dt3.Rows(0)("b_gt_procedure_operated_sys_id"))

                szSql = ""
                szSql &= " select * from sf.b_gt where sys_id = ? "
                Dim dt22 As DataTable = DBacs.ExecuteSql(szSql, -1, dt2.Rows(0)("b_gt_sys_id"))

                If (dt22.Rows(0)("oper_type").ToString().Equals("0") And procedure_no = 3) Or
                    (dt22.Rows(0)("oper_type").ToString().Equals("1") And procedure_no = 2) Then
                    szSql = String.Empty
                    szSql &= "UPDATE sf.b_gt_procedure "
                    szSql &= " SET ng_num = ?,ok_num = ?, update_time=(SELECT CURRENT_TIMESTAMP(6)) "
                    szSql &= " WHERE sys_id = ? "
                    DBacs.ExecuteUpdateSql(szSql, -1, dt33_ng_num, dt33_ok_num, dt3.Rows(0)("b_gt_procedure_sys_id"))
                End If
            Else

            End If

            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
        End Try
        Return rtn
    End Function

End Class
